package com.hongdee.crm.task.service.impl;

import com.hongdee.annotation.MethodComment;
import com.hongdee.crm.task.dao.ITCrmCarPropertyTaskDao;
import com.hongdee.crm.task.entity.TCrmCarProperty;
import com.hongdee.crm.task.service.ITCrmCarPropertyTaskService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by My on 2017/3/15.
 */
@Service
public class TCrmCarPropetyTaskServiceImpl implements ITCrmCarPropertyTaskService {

    @Autowired
    private ITCrmCarPropertyTaskDao carPropertyTaskDao;

    @Override
    @MethodComment(name = "计算新车标签")
    public Integer newCarLabelTaskQuartz()throws Exception {
        String sql=this.newCarLabelSql();
        return this.analyCarList(sql);
    }

    private String newCarLabelSql(){
        return "SELECT t.carId,'新车' as label FROM ( SELECT detail.*, car.id AS carId FROM ( " +
                    "SELECT * FROM t_crm_car_base_detail b " +
                        "WHERE b.tag_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
                        "AND b.base_type = 1 ) detail, t_crm_car car " +
                        "WHERE detail.car_id = car.id " +
                            "GROUP BY detail.company_id, detail.plate_num ) t " +
                        "WHERE " +
                "   NOT EXISTS ( " +
                "SELECT id FROM t_crm_car_workorder w WHERE w.car_id = t.car_id " +
                "AND DATE_FORMAT(w.balance_date, '%Y-%m-%d') > t.tag_date " +
                "AND w.balance_amount > 0 ) limit ?,?";
    }

    /**
     * 社会招揽
     * @throws Exception
     */
    @Override
    @MethodComment(name = "计算社会招揽标签")
    public Integer carSolicitLabelTaskQuartz()throws Exception {
        String sql=this.carSolicitLabelSql();
        return this.analyCarList(sql);
    }

    private String carSolicitLabelSql(){
        return "SELECT t.carId,'社会招揽' as label FROM ( SELECT detail.*, car.id AS carId FROM ( " +
                "SELECT * FROM t_crm_car_base_detail b " +
                "WHERE b.tag_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
                "AND b.base_type = 3 ) detail, t_crm_car car " +
                "WHERE detail.car_id= car.id " +
                "GROUP BY detail.company_id, detail.plate_num ) t " +
                "WHERE " +
                "   NOT EXISTS ( " +
                "SELECT id FROM t_crm_car_workorder w WHERE w.car_id = t.car_id " +
                "AND DATE_FORMAT(w.balance_date, '%Y-%m-%d') > t.tag_date " +
                "AND w.real_amount > '0.00' ) limit ?,?";
    }

    /**
     * 流失标签
     * @throws Exception
     */
    @Override
    @MethodComment(name = "计算车辆流失标签")
    public Integer carLossLabelTaskQuartz() throws Exception {
        return this.analyCarList("select car_id carId,'流失' label from t_crm_car_lost_detail where end_date is NULL GROUP by car_id limit ?,?");
    }

    /**
     * 流失返还
     */
    @Override
    @MethodComment(name = "计算流失返还标签")
    public Integer carLossToBackLabelTaskQuartz() throws Exception {
        return this.analyCarList(this.carLossTobackLabelSql());
    }

    private String carLossTobackLabelSql(){
//        String sql="SELECT b.car_id carId,'流失返还' label FROM " +
//                "t_crm_car_base_detail b " +
//                "WHERE tag_date> DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
//                "AND tag_date IS NOT NULL " +
//                "AND NOT EXISTS ( " +
//                "SELECT id FROM " +
//                "t_crm_car_workorder w " +
//                "WHERE " +
//                "w.car_id = b.car_id " +
//                "AND DATE_FORMAT(balance_date, '%Y-%m-%d') > b.tag_date " +
//                "AND balance_amount> 0 ) limit ?,?";
        String sql="select * from ( " +
                "SELECT " +
                "car_id carId, " +
                "'流失返还' label, " +
                "tag_date " +
                "FROM " +
                "t_crm_car_base_detail " +
                "WHERE " +
                " tag_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
                "AND tag_date IS NOT NULL  AND base_type='2' " +
                "LIMIT ?,? " +
                ")b where  NOT EXISTS ( " +
                "SELECT " +
                "id " +
                "FROM " +
                "t_crm_car_workorder w " +
                "WHERE w.car_id = b.carId " +
                "AND balance_date> UNIX_TIMESTAMP(b.tag_date)*1000 " +
                "AND balance_amount > 0 " +
                ")";
        return sql;
    }

    /**
     * 事故进厂
     */
    @Override
    @MethodComment(name = "计算事故进厂标签")
    public Integer carAccidentLabelTaskQuartz() throws Exception {
        String sql="SELECT DISTINCT car_id carId, '事故进厂' label FROM " +
                "t_crm_car_workorder " +
                "WHERE real_amount > '0.00' " +
                "AND FROM_UNIXTIME(balance_date/1000,'%Y-%m-%d') > DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
                "AND wx_type ='事故车' limit ?,? ";
        return this.analyCarList(sql);
    }

    /**
     * 大于等于三次进厂 活跃用户
     */
    @Override
    @MethodComment(name = "计算活动用户标签，大于等于三次有效进厂")
    public Integer threeFactoryActivityTaskQuartz()throws Exception {
        String sql=this.threeFactoryActivityLabelSql();
        return this.analyCarList(sql);

    }

    /**
     * 清空车辆标签表
     * @throws Exception
     */
    @Override
    public void truncateCrmCarProperty() throws Exception {
        String sql="truncate table t_crm_car_property";
        carPropertyTaskDao.executeSQL(sql);
    }

    /**
     * 获得活跃用户的sql
     * @return
     */
    private String threeFactoryActivityLabelSql(){
        String sql="select car.id carId,'活跃用户' label from t_crm_car car where EXISTS( " +
                "SELECT id,company_id,plate_num, car_id, '活跃用户' FROM " +
                "t_crm_car_workorder " +
                "WHERE " +
                "real_amount> '0.00' " +
                //"  and company_id='8a1029015a227c5a015a2589ab9b0001' " +
                "and car.vin=vin " +
                "and car.company_id=company_id " +
                "AND FROM_UNIXTIME(balance_date/1000,'%Y-%m-%d') > DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
                "GROUP BY " +
                "plate_num " +
                "HAVING " +
                "COUNT( id) > 2) limit ?,?";
        return sql;
    }

    /**
     * 解析获得的数据 分析并插入标签表中
     * @param sql
     * @throws Exception
     */
    private Integer analyCarList(String sql) throws Exception {
        Integer counts=0;
        int start=0;
        int limit=1000;
        while(true) {
            long start1 = System.currentTimeMillis();

            List<Map<String, Object>> lists = carPropertyTaskDao.findCarList(sql,start*limit,limit);
            if (lists == null) {
                start=0;
                break;
            }
            List<TCrmCarProperty> propertyList=new ArrayList<>();
            for (Map<String, Object> map : lists) {
                if (map.get("carId") == null) {
                    continue;
                }
                String carId = String.valueOf(map.get("carId"));
                String label = String.valueOf(map.get("label"));
                //判断标签是否已经存在
//                Integer nums = carPropertyTaskDao.findPropertyByIdAndLabel(carId, label);
//                if (nums <= 0) {
                    //执行插入操作
                    TCrmCarProperty carProperty = new TCrmCarProperty();
                    carProperty.setCarId(carId);
                    carProperty.setLabel(label);
                    propertyList.add(carProperty);
//                    carPropertyTaskDao.saveOrUpdate(carProperty);
//                }
            }
            counts+=propertyList.size();
            carPropertyTaskDao.insertCarPropertyLists(propertyList);
            long end = System.currentTimeMillis();
            System.out.println("运行时间：" + (end - start1) + "毫秒");//应
            start++;
        }
        return counts;
    }

}
